---
title: "pk73102_policy stored procedure documentation"
summary: "Oracle package used by SAIF.Business.Policy to determine submission ownership and proxy policy-status updates."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "WCIS_DBA"
source_name: "pk73102_policy"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name pk73102_policy"
retrieval_date: "2026-08-25"
---
# pk73102_policy
## Overview
| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Policy` |
| **Calling classes** | `PolicyInformation` |
## Purpose
`pk73102_policy` determines whether WCIS owns a submission, exposes PMS-status helper functions, proxies several submission-status changes to the remote `@POLDB` package, and includes a dividend-notepad insert routine.
## Procedures and methods
### does_wcis_own_submission
| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2(1)` ownership flag |
| **Called by** | `PolicyInformation.GetPolicyOwnershipSystem` |
**Description:** Looks up `submission_status_code.wcis_owns_flag` for the submission identified by `in_policy_num`. If no row is found or another exception occurs, the function returns `'Y'`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Policy number whose submission ownership flag is needed. |
### reject_submission
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | No calling class documented in this library* |
**Description:** Pass-through wrapper that forwards the reject request to `WCIS_DBA.PK73102_POLICY.REJECT_SUBMISSION@POLDB`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Submission policy number to reject. |
| `in_rejection_reason_code` | IN | `sub_policy.rejection_reason_code%TYPE` | — | Rejection reason code forwarded to the remote package. |
| `in_update_user` | IN | `sub_policy.update_user%TYPE` | — | User ID recorded for the update. |
### unreject_submission
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | No calling class documented in this library* |
**Description:** Pass-through wrapper that forwards the unreject request to `WCIS_DBA.PK73102_POLICY.UNREJECT_SUBMISSION@POLDB`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Submission policy number to unreject. |
| `in_update_user` | IN | `sub_policy.update_user%TYPE` | — | User ID recorded for the update. |
### set_submission_to_pending
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | No calling class documented in this library* |
**Description:** Pass-through wrapper that forwards the pending-status update to `WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_PENDING@POLDB`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Submission policy number to set to pending. |
| `in_update_user` | IN | `sub_policy.update_user%TYPE` | — | User ID recorded for the update. |
### set_submission_to_quoted
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | No calling class documented in this library* |
**Description:** Pass-through wrapper that forwards the quoted-status update to `WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_QUOTED@POLDB`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Submission policy number to set to quoted. |
| `in_update_user` | IN | `sub_policy.update_user%TYPE` | — | User ID recorded for the update. |
### set_app_received_date
| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` |
| **Called by** | No calling class documented in this library* |
**Description:** Pass-through wrapper that forwards the app-received-date update to `WCIS_DBA.PK73102_POLICY.SET_APP_RECEIVED_DATE@POLDB`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `sub_policy.policy_num%TYPE` | — | Submission policy number to update. |
| `in_update_user` | IN | `sub_policy.update_user%TYPE` | — | User ID recorded for the update. |
### is_pms_status and is_pms_status_yn
| Field | Value |
| --- | --- |
| **Returns** | `BOOLEAN` and `VARCHAR2(1)` |
| **Called by** | No calling class documented in this library* |
**Description:** `is_pms_status` returns `TRUE` only for policy status codes `PP`, `PA`, and `PC`. `is_pms_status_yn` wraps that Boolean result and converts it to `'Y'` or `'N'`.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_status_code` | IN | `VARCHAR2` | — | Policy status code being evaluated. |
### insert_dividend_notepad
| Field | Value |
| --- | --- |
| **Returns** | Procedure; no return value |
| **Called by** | No calling class documented in this library* |
**Description:** Writes a dividend memo entry through `WCIS_DBA.SP72155_INSERT_MEMO_ENTRY`, passing memo code `DVD`, description `Dividend`, caller-supplied note text, and caller-supplied user ID.
#### Parameters
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_div_issue_seq_num` | IN | `NUMBER` | — | Dividend issue sequence number received by the procedure. |
| `in_policy_num` | IN | `NUMBER` | — | Policy number whose memo entry is being written. |
| `in_note_text` | IN | `VARCHAR2` | — | Free-text dividend note content. |
| `in_user` | IN | `VARCHAR2` | — | User ID recorded on the memo entry. |
## Business Rules
### Submission ownership comes from the status code ownership flag
| Field | Value |
| --- | --- |
| **Rule ID** | [BR-POL-006](../business-rules.md#br-pol-006-submission-ownership-comes-from-the-status-code-ownership-flag) |
| **Statement** | Submission ownership is determined by the `wcis_owns_flag` on the matching `submission_status_code` row for the submission's current status. |
| **Trigger** | `does_wcis_own_submission` is called with a policy number that resolves to a `sub_policy` row. |
| **Enforcement** | The function joins `sub_policy` to `submission_status_code` and returns `ssc.wcis_owns_flag`. |
| **Outcome** | Callers receive the status-driven WCIS ownership indicator for the submission. |
### Missing or failed ownership lookups default to WCIS
| Field | Value |
| --- | --- |
| **Rule ID** | [BR-POL-007](../business-rules.md#br-pol-007-missing-or-failed-ownership-lookups-default-to-wcis) |
| **Statement** | When the ownership lookup finds no submission row or raises an unexpected exception, the function returns `'Y'` and treats WCIS as the owner. |
| **Trigger** | `does_wcis_own_submission` hits `NO_DATA_FOUND` or `WHEN OTHERS`. |
| **Enforcement** | Both exception handlers return `'Y'`. |
| **Outcome** | The caller maps the result to `Enums.PolicyOwnershipSystems.WCIS` rather than failing the lookup. |
### PMS status is limited to PA PC and PP
| Field | Value |
| --- | --- |
| **Rule ID** | [BR-POL-008](../business-rules.md#br-pol-008-pms-status-is-limited-to-pa-pc-and-pp) |
| **Statement** | Only policy status codes `PP`, `PA`, and `PC` are treated as PMS statuses, and the wrapper function exposes that result as `Y` or `N`. |
| **Trigger** | `is_pms_status` or `is_pms_status_yn` evaluates a policy status code. |
| **Enforcement** | `is_pms_status` checks membership in `('PP', 'PA', 'PC')`; `is_pms_status_yn` converts the Boolean result into `'Y'` or `'N'`. |
| **Outcome** | PMS-specific callers get a consistent Boolean or Y/N answer from the same code list. |
## Operations
### Determine submission ownership
| Field | Value |
| --- | --- |
| **Purpose** | Return the WCIS ownership indicator for a submission so application code can map it to the correct ownership system enum. |
| **Trigger** | `PolicyInformation.GetPolicyOwnershipSystem` runs `select pk73102_policy.does_wcis_own_submission(:in_policy_num) from dual`. |
| **Key steps** | Join `sub_policy` to `submission_status_code`; return `wcis_owns_flag`; default to `'Y'` on missing data or unexpected errors. |
| **Outputs** | `Y` or `N` ownership flag consumed by the .NET caller. |
### Proxy submission status transitions to POLDB
| Field | Value |
| --- | --- |
| **Purpose** | Forward reject, unreject, pending, quoted, and app-received-date changes to the remote `@POLDB` implementation. |
| **Trigger** | One of the public submission-status functions is called. |
| **Key steps** | Pass through the received policy number, user ID, and any rejection reason to the corresponding `WCIS_DBA.PK73102_POLICY...@POLDB` function. |
| **Outputs** | Numeric return code from the remote package. |
### Insert dividend notepad memo
| Field | Value |
| --- | --- |
| **Purpose** | Create a dividend memo entry for a policy. |
| **Trigger** | `insert_dividend_notepad` is called with dividend issue, policy, note text, and user inputs. |
| **Key steps** | Call `WCIS_DBA.SP72155_INSERT_MEMO_ENTRY` with memo code `DVD`, description `Dividend`, the supplied note text, and the supplied user. |
| **Outputs** | Persisted memo entry created by the downstream procedure. |
## Source Code
PL/SQL package specification
```sql
PACKAGE pk73102_policy
AS
-- FUNCTION get_status_code_descr(in_policy_status_code IN VARCHAR2)
-- RETURN VARCHAR2;
--
-- FUNCTION get_underwriter(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_market_type_code(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_legal_name(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_policy_name(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_policy_num
-- RETURN NUMBER;
--
-- FUNCTION get_status_code(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
--
-- FUNCTION get_rate_acct(in_policy_num IN NUMBER)
-- RETURN VARCHAR2;
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
RETURN VARCHAR2;
FUNCTION reject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_rejection_reason_code IN sub_policy.rejection_reason_code%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION unreject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_submission_to_pending(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_submission_to_quoted(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION set_app_received_date(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER;
FUNCTION is_pms_status(in_policy_status_code IN VARCHAR2)
RETURN BOOLEAN;
FUNCTION is_pms_status_yn(in_policy_status_code IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE insert_dividend_notepad(in_div_issue_seq_num IN NUMBER
, in_policy_num IN NUMBER
, in_note_text IN VARCHAR2
, in_user IN VARCHAR2);
END;
/
```
PL/SQL package body
```sql
PACKAGE BODY pk73102_policy
AS
/* ==========================================================================
Name : does_wcis_own_submission
Prepared By : leedas
Created : 02/06/2008
Description : Indicates when the WCIS system owns the submission submitted online.
Modification History:
========================================================================*/
FUNCTION does_wcis_own_submission(in_policy_num IN sub_policy.policy_num%TYPE)
RETURN VARCHAR2
IS
v_wcis_owns_ind VARCHAR2(1);
BEGIN
SELECT wcis_owns_flag
INTO v_wcis_owns_ind
FROM submission_status_code ssc
, sub_policy s
WHERE s.submission_status_code = ssc.code
AND s.policy_num = in_policy_num;
RETURN v_wcis_owns_ind;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'Y';
WHEN OTHERS THEN
RETURN 'Y';
END does_wcis_own_submission;
/* ==========================================================================
Name : reject_submission
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to reject status.
Modification History:
========================================================================*/
FUNCTION reject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_rejection_reason_code IN sub_policy.rejection_reason_code%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.REJECT_SUBMISSION@POLDB(in_policy_num,in_rejection_reason_code,in_update_user);
END reject_submission;
/* ==========================================================================
Name : unreject_submission
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to non-rejection status.
Modification History:
========================================================================*/
FUNCTION unreject_submission(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.UNREJECT_SUBMISSION@POLDB(in_policy_num,in_update_user);
END unreject_submission;
/* ==========================================================================
Name : set_submission_to_pending
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to pending status.
Modification History:
========================================================================*/
FUNCTION set_submission_to_pending(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_PENDING@POLDB(in_policy_num,in_update_user);
END set_submission_to_pending;
/* ==========================================================================
Name : set_submission_to_quoted
Prepared By : gremil
Created : 03/06/2008
Description : Updates policy table to quoted status.
Modification History:
========================================================================*/
FUNCTION set_submission_to_quoted(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_SUBMISSION_TO_QUOTED@POLDB(in_policy_num,in_update_user);
END set_submission_to_quoted;
/* ==========================================================================
Name : set_app_received_date
Prepared By : gremil
Created : 03/31/2008
Description : Updates policy table.
Modification History:
========================================================================*/
FUNCTION set_app_received_date(in_policy_num IN sub_policy.policy_num%TYPE
, in_update_user IN sub_policy.update_user%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK73102_POLICY.SET_APP_RECEIVED_DATE@POLDB(in_policy_num,in_update_user);
END set_app_received_date;
/* ==========================================================================
Name : is_pms_status
Prepared By : elawoh
Created : 06/22/2009
Description : Determines if policy status is a PMS type.
Called by sp62055_batch_processing.sql, fa100s, fa201s. and ub202.
Modification History:
06/22/09 Written for AR Billing Migration project
Returns True when policy is a PMS policy_status_code PA, PC, PP
========================================================================*/
FUNCTION is_pms_status(in_policy_status_code IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
IF in_policy_status_code IN ('PP', 'PA', 'PC') THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.is_PMS_status(' || in_policy_status_code || '): ' || SQLERRM);
END is_pms_status;
/* ==========================================================================
Name : wrapper to determine if policy status is a pms
Prepared By : elawoh
Created : 07/10/2009
Description : Determines if policy status is a PMS type.
Called by sp62055_batch_processing.sql, fa100s, fa201s, and ub202
Modification History:
07/10/09 Written for AR Billing Migration project
Returns True when policy is a PMS policy_status_code PA, PC, PP
========================================================================*/
FUNCTION is_pms_status_yn(in_policy_status_code IN VARCHAR2)
RETURN VARCHAR2
IS
v_return VARCHAR2(1);
BEGIN
IF is_pms_status(in_policy_status_code) THEN
v_return := 'Y';
ELSE
v_return := 'N';
END IF;
-- DBMS_OUTPUT.put_line('pms yn, return ' || v_policy_status_code);
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.is_PMS_status_yn(' || in_policy_status_code || '): ' || SQLERRM);
END is_pms_status_yn;
/* ==========================================================================
Name : insert_dividend_notepad
Prepared By : carade
Created : 07/05/2012
Description : Insert a notepad for dividends.
Modification History:
========================================================================*/
PROCEDURE insert_dividend_notepad(in_div_issue_seq_num IN NUMBER
, in_policy_num IN NUMBER
, in_note_text IN VARCHAR2
, in_user IN VARCHAR2)
IS
v_final_compute_flag VARCHAR2(1);
CURSOR pp_cur
IS
SELECT pp.policy_period_eff_date
FROM policy_period pp
WHERE pp.policy_num = in_policy_num
AND sp72347_info_page_printed_yn(pp.policy_num
, pp.policy_period_eff_date) = 'Y'
ORDER BY pp.policy_period_eff_date DESC;
pp_rec pp_cur%ROWTYPE;
BEGIN
--
WCIS_DBA.SP72155_INSERT_MEMO_ENTRY(in_policy_num
, pp_rec.policy_period_eff_date
, 'DVD' --in_memo_code
, SYSDATE --in_memo_date
, 'Dividend' --in_memo_descr
, in_note_text
, in_user
, NULL --in_notepad_contact_type_code
, NULL --in_notepad_contact_source_code
);
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'pk73102.insert_dividend_notepad(' || in_policy_num || '): ' || SQLERRM);
END insert_dividend_notepad;
END pk73102_policy;
```
## Calling Code References
| Class | Method | Source File |
| --- | --- | --- |
| `PolicyInformation` | `GetPolicyOwnershipSystem` (Get) | `SAIF.Business.Policy\PolicyInformation.vb` |
## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)
> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.
## Sources
- Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73102_policy.json`
- `SAIF.Business.Policy\PolicyInformation.vb`